﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PSI.Services;
using PSI.Models;
using System.Text;

namespace PSI.Services {
    public class BaseCodeMetadataService:PSIBaseService {

        public object AddMetadta(BaseCodeMetadata model) {

            //检测是否建立过表

            var DatecitionTablestr = new StringBuilder("select count(*) from psi_basecode_metadata where table_name=@table");

            var p = CreateDbParameters();

            p.AddWithValue("table", model.TableName);

            int nums = QueryCount(DatecitionTablestr.ToString(), p);

            if (nums > 0) return false;

            var baseid = Guid.NewGuid();

            WithTransaction(() => {

        
                 //插入psi_basecode_metadata 信息
                 var stradd = new StringBuilder("insert psi_basecode_metadata values (@basecode_id,@category_id,@basecode_name,@table_name,@basecode_memo)");

                 p = CreateDbParameters();
                  
                 p.AddWithValue("basecode_id",baseid);
                 p.AddWithValue("category_id",model.CategoryId);
                 p.AddWithValue("basecode_name",model.BasecodeName);
                 p.AddWithValue("table_name",model.TableName);
                 p.AddWithValue("basecode_memo",model.BasecodeMemo==null?"":model.BasecodeMemo);

                 Execute(stradd.ToString(),p);

                 //创建码表数据表 默认添加几个字段
                 var strcreate = new StringBuilder("create table "+model.TableName+" (bc_id varchar(50) primary key,bc_code varchar(50),bc_name varchar(500),bc_helpcode varchar(50),bc_memo varchar(500),");

                 strcreate.Append("create_user_id varchar(50),create_dt datetime,last_update_user_id varchar(50),last_update_dt datetime)");

                 //p = CreateDbParameters();

                 //p.AddWithValue("users",model.table_name);

                 Execute(strcreate.ToString());

                 //插入psi_basecode_column_metadata 对 那些默认字段的介绍
                 List<object> temp = new List<object>() { new {names="bc_id",zhi="id",type="varchar",lengths="50",visible="0",order="-1"}, 
                    new {names="bc_code",zhi="编码",type="varchar",lengths="50",visible="1",order="1"} ,
                    new {names="bc_name",zhi="码表名",type="varchar",lengths="500",visible="1",order="2"} ,
                    new {names="bc_helpcode",zhi="助记码",type="varchar",lengths="50",visible="1",order="3"}, 
                    new {names="bc_memo",zhi="备注",type="varchar",lengths="500",visible="1",order="4"}, 
                    new {names="create_user_id",zhi="新建用户id",type="varchar",lengths="50",visible="0",order="-1"}, 
                    new {names="create_dt",zhi="新建时间",type="datetime",lengths="",visible="0",order="-1"}, 
                    new {names="last_update_user_id",zhi="最后编辑用户id",type="varchar",lengths="50",visible="0",order="-1"}, 
                    new {names="last_update_dt",zhi="最后编辑时间",type="datetime",lengths="",visible="0",order="-1"} };

                 for (int i = 0; i < temp.Count; i++) {
                     var addcolumnstr = new StringBuilder("insert psi_basecode_column_metadata values (@column_id,@basecode_id,@column_title,@column_name,@column_type,@colmn_length,@default_value,@show_order,@is_visible,@display_fmt)");

                     p = CreateDbParameters();

                     var val = (dynamic)temp[i];

                     p.AddWithValue("column_id", Guid.NewGuid());
                     p.AddWithValue("basecode_id", baseid);
                     p.AddWithValue("column_title", val.zhi);
                     p.AddWithValue("column_name", val.names);
                     p.AddWithValue("column_type", val.type);
                     p.AddWithValue("colmn_length", val.lengths);
                     p.AddWithValue("default_value","");
                     p.AddWithValue("show_order", val.order);
                     p.AddWithValue("is_visible", val.visible);
                     p.AddWithValue("display_fmt","");

                     Execute(addcolumnstr.ToString(),p);
                 }

                 //插入一条fid记录

                 var num = new FIdService().GetMaxNumber("2");

                 if (num == 0) {
                     num = 2001;
                 }

                 var cc = new StringBuilder("insert psi_fid values(@id,@name,@refid)");

                 p = CreateDbParameters();

                 p.AddWithValue("id",num);
                 p.AddWithValue("name", model.BasecodeName);
                 p.AddWithValue("refid",model.BasecodeId);

                 Execute(cc.ToString(), p);

                 //插入psi_permission  权限

                 var strpermiss = new StringBuilder("insert psi_permission values (@permission_id,@permission_name,@fid,@note)");

                 p = CreateDbParameters();

                 p.AddWithValue("permission_id",Guid.NewGuid());
                 p.AddWithValue("permission_name",model.BasecodeName);
                 p.AddWithValue("fid",num);
                 p.AddWithValue("note", model.BasecodeMemo == null ? "" : model.BasecodeMemo);

                 Execute(strpermiss.ToString(), p);

             });


            return new { success = true, id = baseid.ToString() };
        }


        public BaseCodeMetadata GetItemSing(string id) {

            var result = new BaseCodeMetadata();

            var str = new StringBuilder("select basecode_id,category_id,basecode_name,basecode_memo,table_name from psi_basecode_metadata where basecode_id=@id");

            var p = CreateDbParameters();

            p.AddWithValue("id",id);

            Query(str.ToString(), p, ready => {

                result.BasecodeId = ready.GetString(ready.GetOrdinal("basecode_id"));
                result.CategoryId = ready.GetString(ready.GetOrdinal("category_id"));
                result.BasecodeName = ready.GetString(ready.GetOrdinal("basecode_name"));
                result.BasecodeMemo = ready.GetString(ready.GetOrdinal("basecode_memo"));
                result.TableName = ready.GetString(ready.GetOrdinal("table_name"));

            });

            return result;
           
        }


        public List<BaseCodeMetadata> GetItemForField(string name) {

            var result = new List<BaseCodeMetadata>();

            var str = new StringBuilder("select basecode_id,category_id,basecode_name,basecode_memo,table_name from psi_basecode_metadata where basecode_name like '%" + name + "%'");

            Query(str.ToString(),ready => {

                result.Add(new BaseCodeMetadata{ 
                  BasecodeId= ready.GetString(ready.GetOrdinal("basecode_id")),
                  CategoryId=  ready.GetString(ready.GetOrdinal("category_id")),
                  BasecodeName= ready.GetString(ready.GetOrdinal("basecode_name")),
                  BasecodeMemo= ready.GetString(ready.GetOrdinal("basecode_memo")),
                  TableName=   ready.GetString(ready.GetOrdinal("table_name")),
               });
            });

            return result;
        }


        

    }
}